﻿using System;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for SqlDBProxy.
/// </summary>
public class SqlDbProxy:IDisposable
{
    SqlConnection conn;
    SqlCommand cmd;
    SqlDataReader reader;
    SqlTransaction tran;
    public static string ConnectionString;

    public SqlDbProxy(string storedprocedurename)
    {
        conn = new SqlConnection(SqlDbProxy.ConnectionString);
        cmd = new SqlCommand(storedprocedurename, conn);
        cmd.CommandType = CommandType.StoredProcedure;
    }

    public SqlDbProxy(SqlCommand command)
    {
        conn = new SqlConnection(SqlDbProxy.ConnectionString);
        cmd = command;
        cmd.Connection = conn;
    }
    /// <summary>
    /// For Transaction
    /// </summary>
    public SqlDbProxy()
    {
        conn = new SqlConnection(SqlDbProxy.ConnectionString);
        conn.Open();
    }
    /// <summary>
    /// For stored procedure transaction
    /// </summary>
    public void BeginTransaction()
    {
        tran = conn.BeginTransaction();
        cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Transaction = tran;
    }
    public void BeginTransactionSQL()
    {
        tran = conn.BeginTransaction();
        cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.Transaction = tran;
    }
    public void NewTranCommand(string storedProcedureName)
    {
        cmd.Parameters.Clear();
        cmd.CommandText = storedProcedureName;
    }
    /// <summary>
    /// For Transaction
    /// </summary>
    public void Commit()
    {
        tran.Commit();
    }
    public void Rollback()
    {
        tran.Rollback();
    }
    public DataSet GetDataSet()
    {
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adp.Fill(ds);
        conn.Close();
        return ds;
    }
    public void Fill(ref DataSet dataset)
    {
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(dataset);
    }

    public SqlParameter AddParameter(string parameterName, SqlDbType sqldbtype, int size)
    {
        return cmd.Parameters.Add(parameterName, sqldbtype, size);
    }
    public SqlParameter AddParameter(string parameterName, SqlDbType sqldbtype)
    {
        return cmd.Parameters.Add(parameterName, sqldbtype);
    }
    public SqlParameter AddParameter(string parameterName, object pvalue)
    {
        return cmd.Parameters.AddWithValue(parameterName, pvalue);
    }
    public SqlDataReader ExecuteReader()
    {
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }
        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return reader;
    }
    /// <summary>
    /// For both transaction and nontransaction.
    /// </summary>
    public int ExecuteNonQuery()
    {
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }
        return cmd.ExecuteNonQuery();
    }
    public void Close()
    {
        if (reader != null)
        {
            reader.Close();
        }
        conn.Close();
    }
    public void Dispose()
    {
        if (reader != null)
        {
            reader.Close();
        }
        conn.Close();
    }

}

